package db_layer;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import model_layer.Customer;

/**
 * This class provides methods required to access data in the database that is required for performing operations on customers.
 * @author Rafal Markiewicz
 *
 */
public class CustomerDB
{
	private Connection con;
	
	/**
	 * The constructor of DBCustomer gets an instance of a Connection from the DBConnection class.
	 * Since DBConnection is a singleton it is always the same Connection that is provided by it.
	 */
	public CustomerDB()
	{
		con = DBConnection.get_instance().get_connection();
	}
	
	/**
	 * This function finds a customer by an integer field (e.g id)
	 * @param var_name -> name of the variable by which we are searching for a customer
	 * @param var -> value of the variable by which we are searching for the customer
	 */
	public Customer find_customer(String var_name, int var)
	{
		return single_find(var_name, "", var);
	}
	
	/**
	 * This function finds a customer by a String field (e.g name)
	 * @param var_name -> name of the variable by which we are searching for a customer
	 * @param var -> value of the variable by which we are searching for the customer
	 */
	public Customer find_customer(String var_name, String var)
	{
		return single_find(var_name, var, -1);
	}
	
	public List<Customer> find_all_customers()
	{
		return multiple_find("", "", -1);
	}
	
	public List<Customer> find_particular_customers(String var_name, int var)
	{
		return multiple_find(var_name, "", var);
	}
	
	public String find_city(String zipcode)
	{
		String query = DBUtilities.make_query("city", "ZipCity");
		ResultSet results;
		try
		{
			PreparedStatement ps = DBUtilities.prepare_statement(con, query, "zipcode", zipcode);
			ps.setQueryTimeout(5);
			results = ps.executeQuery();
			if(results.next())
			{
				return results.getString("city");
			}
			ps.close();
		}
		catch(SQLException e)
		{
			System.err.println("Error occured while searching for city: " + e);
		}
		return null;
	}
	
	/**
	 * This method inserts a new customer into a database.
	 * @param cust -> customer to be inserted into the database, holding all necessary data for performing this operation
	 * @return true if insert was successful, false otherwise
	 */
	public boolean insert_customer(Customer cust)
	{
		boolean success = false;	//this variable states whether we were successful in inserting a customer, it will change to true if we achieve a success
												
		String phone_nr = cust.getPhone_nr();	//here we take all of customer's data and put it into variables
		String address = cust.getAddress();		//it is not necessary but improves code readability and in general is helpful :)
		String city = cust.getCity();
		float discount = cust.getDiscount();
		String email = cust.getEmail();
		String name = cust.getName();
		String zipcode = cust.getZipcode();
		boolean deleted = cust.isDeleted();
		
		try
		{
			/*
			 * Here we use transactions, because inserting a customer requires more than one insert statement, 
			 * so it is only acceptable to enter all of the customer information, or none of it because partial insert would make db inconsistent
			 *  (like being pregnant - either you are or you are not, there is nothing in the middle)
			 */
			DBConnection.begin_transaction();	//here we begin a transaction -> nothing will be committed until we end it
			insert_update_zipcode(zipcode, city);	//insert the zipcode into ZipCity table if it is not there, and if it is then update it
			//Here we create an insert statement with our convenience method from DBUtilities class.
			PreparedStatement ps = DBUtilities.make_insert_statement(con, "Customers", "phone_nr, cust_address, discount, email, name, zipcode, deleted");	
				
			ps.setString(1, phone_nr);		//Here we set all variables of the PreparedStatement. They need to be set before we can execute the statement.
			ps.setString(2, address);		//We are using PreparedStatements because they are resistant to SQL Injection attacks
			ps.setFloat(3, discount);		//This cool feature however comes at a price of setting every variable in the statement beforehand
			ps.setString(4, email);
			ps.setString(5, name);
			ps.setString(6, zipcode);
			ps.setBoolean(7, deleted);
			ps.setQueryTimeout(5);			//Here we set a timeout for the query - it is not strictly required, but good to set some reasonable timeout for these.
			ps.executeUpdate();				//Here we execute update(insert in this particular case, but the method is the same)
			ps.close();						//Close statement, so we don;t have a resource leak
			DBConnection.end_transaction();	//Finally all statements were executed so we can end the transaction and committ the changes, they will either work together
											//or fail together
			success = true;					//If we got here, then everything went ok and we can mark it as a success :)
			
		}
		catch(SQLException e)	//if sth got wrong we get an exception
		{
			System.err.println("Error while inserting a customer: " + e);	//Print an error so we know that sth went wrong
			DBConnection.rollback_transaction();	//Rollback - which means that we cancel all the changes that the statements done during transaction could have made
													//In general this is a failure that should be investigated, but at least we didn't destroy our db.
		}
		return success;	//return whether we managed to insert or not
	}
	
	public boolean update_customer(Customer cust)
	{
		int rc = -1;
		int id = Integer.parseInt(cust.getId());
		String phone_nr = cust.getPhone_nr();
		String address = cust.getAddress();
		String city = cust.getCity();
		float discount = cust.getDiscount();
		String email = cust.getEmail();
		String name = cust.getName();
		String zipcode = cust.getZipcode();
		boolean deleted = cust.isDeleted();
		
		try
		{
			DBConnection.begin_transaction();
			insert_update_zipcode(zipcode, city);
			
			PreparedStatement ps = DBUtilities.make_update_statement(con, "Customers", "phone_nr, cust_address, discount, email, name, zipcode, deleted","id");
			ps.setString(1, phone_nr);
			ps.setString(2, address);
			ps.setFloat(3, discount);
			ps.setString(4, email);
			ps.setString(5, name);
			ps.setString(6, zipcode);
			ps.setBoolean(7, deleted);
			ps.setInt(8, id);
			ps.setQueryTimeout(5);
			rc = ps.executeUpdate();
			ps.close();
			DBConnection.end_transaction();
		}
		catch(SQLException e)
		{
			System.err.println("Error while updating a customer: " + e);
			DBConnection.rollback_transaction();
			
		}
		return rc == 1;
	}
	
	private void insert_update_zipcode(String zipcode, String city) throws SQLException
	{
		String query = "IF exists (SELECT * FROM ZipCity WHERE zipcode = ?) "
						+	"BEGIN "
						+	   "UPDATE ZipCity SET city = ? "
						+	   "WHERE zipcode = ? "
						+	"END "
						+	"ELSE "
						+	"BEGIN "
						+	   "INSERT INTO ZipCity(zipcode, city) VALUES (?, ?) "
						+	"END";

		PreparedStatement ps = con.prepareStatement(query);
		ps.setString(1, zipcode);
		ps.setString(2, city);
		ps.setString(3, zipcode);
		ps.setString(4, zipcode);
		ps.setString(5, city);
		ps.setQueryTimeout(5);
		ps.executeUpdate();
		ps.close();
	}
	
	
	/**
	 * This function finds a single customer, matching desired conditions, in the database.
	 * @param var_name -> name of the variable by which a specific customer is to be found
	 * @param s_var ->this is a String value of the variable, if it is empty than the i_var will be taken under consideration
	 * @param i_var -> this is an integer value of the variable, considered only if s_var is an empty String
	 * @return cust -> found customer. If more than one customer matches, it returns the first one found.
	 * 					If there was an error while running a function or if no matches were found the function returns null.
	 */
	private Customer single_find(String var_name, String s_var, int i_var)
	{
		String query = make_query(); 	// the function assigns to query a typical query for customers
		ResultSet results; 				// a ResultSet that later on will be used is declared
		Customer cust = null;
		try
		{
			/* A PreparedStatement is created with UtilityFunctions method prepare_statement
			 PreparedStatement is much better than a normal Statement because it is resistant to SQL Injections
			 making it much more secure mean of accessing the database than a normal Statement.
			 A statement is a java representation of a query - it works the same as an SQL query. */
			PreparedStatement ps = DBUtilities.prepare_statement(con, query, var_name, s_var, i_var);
			ps.setQueryTimeout(5);
			results = ps.executeQuery();	//Here we execute our statement and assign the results to the ResultSet we declared before
			if(results.next())		
			{
				cust = create_customer(results);	//If at least on result exists we use the results of query to create a customer object
			}
			ps.close();			//We have to close the statement after all operations on the ResultSet have been finished
		}
		catch(SQLException se)
		{
			System.err.println("Customer query exception: " + se);	//If something goes wrong, we print an exception
		}
		return cust;	
	}
	
	/**
	 * This function finds all customers matching desired conditions in the database.
	 * If there was an error while running a function or if no matches were found the function returns an empty ArrayList.
	 */
	private List<Customer> multiple_find(String var_name, String s_var, int i_var)
	{
		String query = make_query();
		ResultSet results;
		List<Customer> customers = new ArrayList<Customer>();
		Customer cust = null;
		try
		{
			PreparedStatement ps = DBUtilities.prepare_statement(con, query, var_name, s_var, i_var);
			ps.setQueryTimeout(5);
			results = ps.executeQuery();
			while(results.next())
			{
				cust = create_customer(results);	//while there are results we create customers from ResultSet 
				customers.add(cust);				//and add them to our ArrayList
			}
			ps.close();
		}
		catch(SQLException se)
		{
			customers.clear();		//if an error occurred we clear the ArrayList from the extracted data, so that we return an empty ArrayList
			System.err.println("Customers query exception: " + se);
		}
		return customers;
	}
	
	/**
	 * This function creates a query for searching database for a desired customer.
	 */
	private String make_query()
	{
		return DBUtilities.make_query("c.id, c.name, c.phone_nr, c.email, c.cust_address, c.zipcode, z.city, c.discount, c.deleted",
				"ZipCity AS z, Customers AS c", " c.zipcode = z.zipcode");
	}
	
	/**
	 * This function creates a customer using a ResultSet and returns him. 
	 * If there was an error while creating a customer the function returns null.
	 */
	private Customer create_customer(ResultSet results)
	{
		Customer cust = new Customer();

		try
		{
			/*Here we set fields of a customer with data extracted from the ResultSet.
			 We get them by the names of the fields from the select statements which are names of the columns unless we use the AS statement
			 (e.g if query was SELECT id ... we use: results.getInt("id"), but if it was SELECT id AS cust_id... we would use: results.getInt("cust_id"),
			 note that SELECT e.id ...  will still require to use results.getInt("id"), not results.getInt("e.id"))*/
			cust.setId(String.valueOf(results.getInt("id")));
			cust.setName(results.getString("name"));
			cust.setAddress(results.getString("cust_address"));
			cust.setZipcode(results.getString("zipcode"));
			cust.setCity(results.getString("city"));
			cust.setPhone_nr(results.getString("phone_nr"));
			cust.setEmail(results.getString("email"));
			cust.setDiscount(results.getFloat("discount"));
			cust.setDeleted(results.getBoolean("deleted"));
			
		}
		catch(SQLException s)
		{
			System.out.println("Error while creating a customer: " + s);
			return null;
		}
		
		return cust;
	}
	
	public void check_for_regular()
	{
		ResultSet results;
		try
		{
			DBConnection.begin_transaction();
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery("SELECT * FROM Customers WHERE id = 0");
			if(!results.next())
			{
				Statement ins_stmt = con.createStatement();
				ins_stmt.setQueryTimeout(5);
				ins_stmt.executeUpdate("INSERT INTO ZipCity(zipcode, city) VALUES('', '') SET IDENTITY_INSERT Customers ON"
						+ " INSERT INTO Customers(id, name, discount, deleted, zipcode) VALUES(0, 'Regular Customer', 0, 0, '') "
						+ "SET IDENTITY_INSERT Customers OFF");
				ins_stmt.close();
			}
			stmt.close();
			DBConnection.end_transaction();
			
		}
		catch(SQLException e)
		{
			System.out.println("Error while creating Regular Customer: " + e);
			DBConnection.rollback_transaction();
		}
	}

}
